/**
 * PPH平均值计算器
 * 实现增量更新和范围更新功能
 */

const path = require('path');

// 使用统一的数据库连接池
const DatabasePool = require('../utils/dbPool.cjs');
const dbPath = path.join(__dirname, '../data/pph.sqlite');
const dbPool = new DatabasePool(dbPath);

/**
 * 计算指定分组的PPH平均值
 * 公式: PPH平均值 = SUM(完工数量) ÷ (AVG(人数) × SUM(净工作时间) / 60)
 */
async function calculatePPHAverage(recordDate, lineName, materialCode) {
  try {
    const sql = `
      SELECT
        SUM(CASE WHEN quantity IS NOT NULL THEN quantity ELSE 0 END) as total_quantity,
        SUM(CASE WHEN net_working_minutes IS NOT NULL THEN net_working_minutes ELSE 0 END) as total_minutes,
        AVG(CASE WHEN total_people IS NOT NULL THEN total_people ELSE 0 END) as avg_people,
        COUNT(*) as record_count
      FROM pph_records
      WHERE record_date = ? AND line_name = ? AND material_code = ?
    `;

    const rows = await dbPool.query(sql, [recordDate, lineName, materialCode]);
    const row = rows && rows[0];

    if (!row || row.total_minutes === 0 || row.avg_people === 0) {
      return null;
    }

    // PPH平均值 = SUM(完工数量) ÷ (AVG(人数) × SUM(净工作时间) / 60)
    const averagePPH = row.total_quantity / (row.total_minutes / 60 * row.avg_people);
    return {
      average_pph: Math.round(averagePPH * 100) / 100,
      total_quantity: row.total_quantity,
      total_minutes: row.total_minutes,
      avg_people: row.avg_people,
      record_count: row.record_count
    };
  } catch (error) {
    console.error('计算PPH平均值失败:', error);
    return null;
  }
}

/**
 * 增量更新PPH平均值（指定天数范围）
 */
async function updatePPHAverageIncrementally(days = 7, progressCallback = null) {
  try {
    console.log(`📊 开始增量更新PPH平均值 (范围: ${days}天)`);
    
    // 1. 获取需要更新的日期范围
    const endDate = new Date();
    const startDate = new Date();
    startDate.setDate(endDate.getDate() - days);
    
    const startDateStr = startDate.toISOString().split('T')[0];
    const endDateStr = endDate.toISOString().split('T')[0];
    
    console.log(`📅 更新范围: ${startDateStr} 至 ${endDateStr}`);
    
    // 2. 获取需要更新的分组
    const groupsSql = `
      SELECT DISTINCT record_date, line_name, material_code
      FROM pph_records
      WHERE record_date >= ? AND record_date <= ?
      ORDER BY record_date DESC, line_name, material_code
    `;
    
    const groups = await dbPool.query(groupsSql, [startDateStr, endDateStr]) || [];

    console.log(`🔍 找到 ${groups.length} 个分组需要更新`);

    // 初始化进度
    if (progressCallback) {
      progressCallback(0, groups.length);
    }

    // 3. 逐个分组计算并更新PPH平均值
    let updatedCount = 0;
    let groupCount = 0;
    
    for (const group of groups) {
      try {
        // 计算PPH平均值
        const pphData = await calculatePPHAverage(group.record_date, group.line_name, group.material_code);
        
        if (pphData !== null) {
          // 更新该分组的所有记录
          const updateSql = `
            UPDATE pph_records
            SET average_pph = ?, updated_at = CURRENT_TIMESTAMP
            WHERE record_date = ? AND line_name = ? AND material_code = ?
          `;

          const updateResult = await dbPool.query(updateSql, [
            pphData.average_pph,
            group.record_date,
            group.line_name,
            group.material_code
          ]);
          const changesCount = updateResult?.changes || 0;

          updatedCount += changesCount;
          groupCount++;

          console.log(`✅ 更新分组 [${group.record_date}|${group.line_name}|${group.material_code}]: PPH=${pphData.average_pph}, 影响${changesCount}条记录`);
        }

        // 更新进度
        if (progressCallback) {
          progressCallback(groupCount, groups.length);
        }

      } catch (error) {
        console.error(`❌ 更新分组失败 [${group.record_date}|${group.line_name}|${group.material_code}]:`, error);
      }
    }
    
    console.log(`✅ 增量更新完成: 更新了${groupCount}个分组，共${updatedCount}条记录`);
    
    return {
      updatedCount,
      groupCount,
      dateRange: { start: startDateStr, end: endDateStr }
    };
    
  } catch (error) {
    console.error('增量更新PPH平均值失败:', error);
    throw error;
  }
}

/**
 * 全量更新PPH平均值（指定日期范围）
 */
async function calculatePPHAverageForRange(startDate, endDate, progressCallback = null) {
  try {
    console.log(`📊 开始范围更新PPH平均值: ${startDate} 至 ${endDate}`);

    // 获取指定范围内的所有分组
    const groupsSql = `
      SELECT DISTINCT record_date, line_name, material_code
      FROM pph_records
      WHERE record_date >= ? AND record_date <= ?
      ORDER BY record_date DESC, line_name, material_code
    `;

    const groups = await dbPool.query(groupsSql, [startDate, endDate]) || [];

    console.log(`🔍 找到 ${groups.length} 个分组需要计算`);

    // 初始化进度
    if (progressCallback) {
      progressCallback(0, groups.length);
    }

    let updatedCount = 0;
    let groupCount = 0;

    for (const group of groups) {
      try {
        const pphData = await calculatePPHAverage(group.record_date, group.line_name, group.material_code);

        if (pphData !== null) {
          const updateSql = `
            UPDATE pph_records
            SET average_pph = ?, updated_at = CURRENT_TIMESTAMP
            WHERE record_date = ? AND line_name = ? AND material_code = ?
          `;

          const updateResult = await dbPool.query(updateSql, [
            pphData.average_pph,
            group.record_date,
            group.line_name,
            group.material_code
          ]);
          const changesCount = updateResult?.changes || 0;

          updatedCount += changesCount;
          groupCount++;
        }

        // 更新进度
        if (progressCallback) {
          progressCallback(groupCount, groups.length);
        }

      } catch (error) {
        console.error(`❌ 计算分组失败 [${group.record_date}|${group.line_name}|${group.material_code}]:`, error);
      }
    }
    
    console.log(`✅ 范围更新完成: 计算了${groupCount}个分组，共${updatedCount}条记录`);
    
    return {
      updatedCount,
      groupCount,
      dateRange: { start: startDate, end: endDate }
    };
    
  } catch (error) {
    console.error('范围更新PPH平均值失败:', error);
    throw error;
  }
}

/**
 * 一键重算PPH平均值（默认一个月）
 */
async function oneClickRecalculate(options = {}) {
  const {
    days = 30,           // 默认一个月
    fullRange = false,   // 是否全量重算
    progressCallback = null  // 进度回调函数
  } = options;

  try {
    if (fullRange) {
      // 全量重算：获取数据库中的最早和最晚日期
      const rangeSql = `
        SELECT
          MIN(record_date) as min_date,
          MAX(record_date) as max_date
        FROM pph_records
      `;

      const rangeRows = await dbPool.query(rangeSql, []);
      const range = rangeRows && rangeRows[0];

      if (range && range.min_date && range.max_date) {
        return await calculatePPHAverageForRange(range.min_date, range.max_date, progressCallback);
      } else {
        throw new Error('无法获取数据范围');
      }
    } else {
      // 指定天数重算
      return await updatePPHAverageIncrementally(days, progressCallback);
    }
  } catch (error) {
    console.error('一键重算PPH平均值失败:', error);
    throw error;
  }
}

module.exports = {
  calculatePPHAverage,
  updatePPHAverageIncrementally,
  calculatePPHAverageForRange,
  oneClickRecalculate
};
